package com.crecheng.shop.util;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.*;

public final class ConSQL {
    static Connection connection=null;
    static Queue<Statement> statements =null;
    static String url="jdbc:mysql://localhost:3306/mybatis";
    static String user="mybatis";
    static String password="123456";

    static Connection ConnectionSQL(){
        Statement statement=null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection=DriverManager.getConnection(url,user,password);
            statement=connection.createStatement();
            if(statements ==null)
                statements =new LinkedList<>();
        }catch (Exception e){
            System.out.println("Connection fail!");
            System.out.println(Arrays.toString(e.getStackTrace()));
        }
        finally {
            if(statement!=null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        return connection;
    }

    public static  <T> List<T> select(Class<T> objClass, String databaseName, String Other) {
        List<T> res = new ArrayList<>();

        if (databaseName == null)
            databaseName = objClass.getSimpleName().toLowerCase();

        if (!check()) {
            System.out.println("数据库无连接");
            return res;
        }

        var fieldsL = getAllFields(objClass);

        int i = 0;
        Map<String, Field> fields = new HashMap<>();
        for (Field f : fieldsL) {
            fields.put(f.getName().toLowerCase(), f);
        }

        List<String> colNames = getColumnName(databaseName,true);
        Statement statement=getState();
        ResultSet resultSet=null;
        try {
            String cmd = "select * from " + databaseName + ((Other != null && Other.length() > 1) ? " where " + Other : "");
            try {
                resultSet = statement.executeQuery(cmd);
            } catch (Exception ex) {
                System.out.println("sql: " + cmd);
                ex.printStackTrace();
            }

            Map<String, Object> map = new HashMap<String, Object>();
            while (resultSet.next()) {
                map.clear();
                for (var colName : colNames) {
                    if (fields.containsKey(colName)) {
                        String typeName = fields.get(colName).getType().getName();
                        Object con = null;
                        switch (typeName) {
                            case "int":
                                con = resultSet.getInt(colName);
                                break;
                            case "java.lang.String":
                                con = resultSet.getString(colName);
                                break;
                            case "float":
                                con = resultSet.getFloat(colName);
                                break;
                            case "java.sql.Date":
                                con = resultSet.getDate(colName);
                                break;
                            case "java.sql.Time":
                                con = resultSet.getTime(colName);
                                break;
                            case "java.sql.Timestamp":
                                con = resultSet.getTimestamp(colName);
                                break;
                        }
                        map.put(colName, con);
                    }
                }
                try {
                    T newInstance = null;
                    try {
                        newInstance = objClass.getDeclaredConstructor().newInstance();
                    } catch (NoSuchMethodException e) {
                        e.printStackTrace();
                    }
                    int k = 0;
                    for (var c : colNames) {
                        if (map.containsKey(c)) {
                            var f = fields.get(c);
                            Object obj = map.get(c);
                            f.setAccessible(true);
                            f.set(newInstance, obj);
                        }
                    }
                    res.add(newInstance);
                } catch (InstantiationException | IllegalAccessException | InvocationTargetException e) {
                    e.printStackTrace();
                }

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeResult(statement);
        }
        return res;
    }

    public static  <T> List<T> select(Class<T> objClass, String databaseName, Map<String,String> other){
        StringBuilder sqlOther=new StringBuilder();
        if(other!=null&&other.size()>0) {
            sqlOther.append(" where ");
            other.forEach((key, value) -> {
                sqlOther.append(key).append("=").append("'").append(value).append("' and ");
            });
            sqlOther.replace(sqlOther.length() - 5, sqlOther.length() - 1, "");
        }
        return select(objClass,databaseName,sqlOther.toString());
    }

    public static  <T> List<T> select(Class<T> objClass, Map<String,String> other){
        return select(objClass,null,other);
    }

    public static  <T> List<T> select(Class<T> objClass){
        return select(objClass,null,"");
    }

    public static  <T> List<T> select(Class<T> objClass, String Other){
        return select(objClass,null,Other);
    }

    public static  <T> List<T> select(Class<T> objClass, int offset,int count){
        return select(objClass,"1=1",offset,count);
    }

    public static  <T> List<T> select(Class<T> objClass,String other, int offset,int count){
        return select(objClass,null,other+ " "+offset+" , "+count);
    }

    public static <T> int insert(Class<T> Class, String databaseName, List<T> data){
        if(!check())
            return -1;
        if (databaseName == null)
            databaseName= Class.getSimpleName();

        var colName= getColumnName(databaseName);
        StringBuilder col;
        StringBuilder value;
        var fields=getAllFields(Class);
        Map<String,Field> fieldMap=new HashMap<>();
        int count=0;
        for (var f:fields){
            fieldMap.put(f.getName().toLowerCase(),f);
        }
        for(var d:data){
            int i=0;
            col=new StringBuilder();
            value=new StringBuilder();

            for (var c:colName){
                if(i++!=0){
                    col.append(",");
                    value.append(",");
                }
                col.append(c);

                var field=fieldMap.get(c);
                field.setAccessible(true);
                Object obj=null;
                try {
                    obj=field.get(d);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                if(obj!=null)
                    value.append("'").append(obj.toString()).append("'");
                else
                    value.append("NULL");
            }
            String sql="insert into "+databaseName+ " ("+col+ ") VALUES ("+value+ ")";
            Statement statement=getState();
            try {
                if( statement.executeUpdate(sql)>0)
                    count++;
                else
                    System.out.println("false");
            } catch (SQLException throwables) {
                System.out.println(sql);
                throwables.printStackTrace();
            }finally {
                closeResult(statement);
            }

        }
        return count;
    }

    public static <T> int insert(Class<T> Class, T obj){
        List<T> l=new ArrayList<>();
        l.add(obj);
        return insert(Class,l);
    }

    public static <T> int insert(Class<T> Class, List<T> data){
        return insert(Class,null,data);
    }

    public static int delete(Class<?> Class, String databaseName, Map<String,String> data){
        if(!check())
            return -1;
        if (databaseName == null)
            databaseName= Class.getSimpleName();
        String sql="delete from "+databaseName+(data!=null?" where ":"");
        StringBuilder other=new StringBuilder();
        if(data!=null) {
            data.forEach((key, value) -> {
                other.append(key).append("=").append("'").append(value).append("' and ");
            });
            other.replace(other.length()-5,other.length()-1,"");
        }
        sql+=other.toString();
        Statement statement=getState();
        try {
            return statement.executeUpdate(sql);
        } catch (SQLException throwables) {
            System.out.println(sql);
            throwables.printStackTrace();
        }
        finally {
            closeResult(statement);
        }
        return -1;
    }

    public static int delete(Class<?> Class, Map<String,String> data){
        return delete(Class,null,data);
    }

    public static int delete(Class<?> Class , String databaseName, String other){
        if(!check())
            return -1;
        if (databaseName == null)
            databaseName= Class.getSimpleName();
        String sql="delete from "+databaseName+(other!=null?" where "+other:"");
        Statement statement=getState();
        try {
            return statement.executeUpdate(sql);
        } catch (SQLException throwables) {
            System.out.println(sql);
            throwables.printStackTrace();
        }
        finally {
            closeResult(statement);
        }
        return -1;
    }

    public static int delete(Class<?> Class , String other){
        return delete(Class,null,other);
    }

    public static <T> int update(Class<T> Class, String databaseName, T oldObj, T newObj){
        if(!check())
            return -1;
        if (databaseName == null)
            databaseName= Class.getSimpleName();
        var colName=getColumnName(databaseName);
        var fields=getAllFields(Class);
        Map<String,Field> fieldMap=new HashMap<>();
        int count=0;
        for (var f:fields){
            fieldMap.put(f.getName().toLowerCase(),f);
        }
        String sql="update "+databaseName+" set ";
        String set="";
        String old=" ";
        int i=0;
        int j=0;
        for (var c:colName){

            var field=fieldMap.get(c);
            field.setAccessible(true);
            Object obj=null;
            Object obj1=null;
            try {
                obj=field.get(newObj);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            if(i++!=0&&obj!=null)
                set+=",";
            if(obj!=null)
                set+=c+"='"+obj.toString()+"' ";
            else
                set+=c+"=NULL";

            try {
                obj1=field.get(oldObj);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            if(j++!=0&&obj1!=null)
                old+="and ";
            if(obj1!=null)
                old+=c+"='"+obj1.toString()+"' ";

        }
        sql+=set+" where "+old;

        //var tmp= selectSQL(Class,null," where "+old);
        Statement statement=getState();
        try {
            return statement.executeUpdate(sql);
        } catch (SQLException throwables) {
            System.out.println(sql);
            throwables.printStackTrace();
        }
        finally {
            closeResult(statement);
        }
        return -1;
    }

    public static <T> int update(Class<T> Class, String databaseName, String where, T newObj){
        if(!check())
            return -1;
        if (databaseName == null)
            databaseName= Class.getSimpleName();
        var colName=getColumnName(databaseName);
        var fields=getAllFields(Class);
        Map<String,Field> fieldMap=new HashMap<>();
        int count=0;
        for (var f:fields){
            fieldMap.put(f.getName().toLowerCase(),f);
        }
        String sql="update "+databaseName+" set ";
        String set="";
        int i=0;
        for (var c:colName){

            var field=fieldMap.get(c);
            field.setAccessible(true);
            Object obj=null;
            try {
                obj=field.get(newObj);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            if(i++!=0)
                set+=",";
            if(obj!=null)
                set+=c+"='"+obj.toString()+"' ";
            else
                set+=c+"=NULL";

        }
        sql+=set+" where "+where;

        //var tmp= selectSQL(Class,null," where "+old);
        Statement statement=getState();
        try {
            return statement.executeUpdate(sql);
        } catch (SQLException throwables) {
            System.out.println(sql);
            throwables.printStackTrace();
        }
        finally {
            closeResult(statement);
        }
        return -1;
    }

    public static <T> int update(Class<T> Class, String where, T newObj){
        return update(Class,null,where,newObj);
    }

    public static List<String> getColumnName(String tableName, boolean havePkey){
        if(!check())
            return null;
        String sql="select column_name from information_schema.columns where table_name='"+tableName+"'";
        ResultSet res=null;
        List<String> re=new ArrayList<>();
        Statement statement=getState();
        try {
            res= statement.executeQuery(sql);

            while (res.next()){
                re.add( res.getString(1).toLowerCase());
            }

            if(!havePkey) {
                var pkName = getPKey(tableName);
                if (pkName != null)
                    re.remove(pkName);
            }

            return re;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return re;
        }
        finally {
            closeResult(statement);
        }
    }

    public static List<String> getColumnName(String tableName){
        return getColumnName(tableName,false);
    }

    public static String getPKey(String table){
        if(!check())
            return null;
        String sql="SELECT COLUMN_NAME\n" +
                "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE\n" +
                "WHERE TABLE_NAME='"+table+"'";
        ResultSet res=null;
        Statement statement=getState();
        try {
            res= statement.executeQuery(sql);
            if(res.next())
                return res.getString(1);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
        finally {
            closeResult(statement);
        }
        return null;
    }

    public static boolean check(){
        if(connection!=null)
            return true;
        else {
             ConnectionSQL();
             if(connection==null)
                 return false;
             else return true;
        }
    }

    static synchronized Statement getState() {
        try {
            if(statements.size()>0)
                return statements.poll();
            return connection.createStatement();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
    }

    static void closeResult(Statement state){
        if(state!=null){
            try {
                var r=state.getResultSet();
                if(r!=null&&!r.isClosed())
                    r.close();
                statements.offer(state);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
    }

    static List<Field> getAllFields(Class<?> Class){
        ArrayList<Field> list=new ArrayList<>();
        getAllFields(Class,list);
        return list;
    }

    static void getAllFields(Class<?> Class,List<Field> list){
        list.addAll(Arrays.asList(Class.getDeclaredFields()));
        var superClass=Class.getSuperclass();
        if(superClass!=Object.class)
            getAllFields(superClass,list);
    }

}
